use super::db::get_pool;
use super::request::{DeviceInfo, DeviceSearch};
use super::response::{DeviceInfoRes, DeviceList};

use sqlx::{self};

// 创建 device
pub async fn create_device(data: &DeviceInfo) -> Result<(), sqlx::Error> {
    let pool = get_pool().unwrap();
    let res= sqlx::query("INSERT INTO tb_device (name, model, device_type,power_type, power_value,price,device_img,remark,created_at, updated_at) VALUES (?, ?, ?, ?, ?, ?, ?,?,NOW(), NOW())")
    .bind(&data.name)
    .bind(&data.model)
    .bind(&data.device_type)
    .bind(&data.power_type)
    .bind(&data.power_value)
    .bind(&data.price)
    .bind(&data.device_img)
    .bind(&data.remark)
    .execute(pool).await;

    match res {
        Ok(i) => println!("ok {:#?}", i),
        Err(e) => println!("err {:#?}", e),
    }
    Ok(())
}

// 修改device
pub async fn edit_device(data: &DeviceInfo) -> Result<(), sqlx::Error> {
    let pool = get_pool().unwrap();
    let sql_text = "UPDATE tb_device SET name = ?, model = ?, device_type = ?, power_type = ?, power_value = ?, price = ?, device_img = ?, remark = ?, updated_at = NOW() WHERE id = ?";
    let res = sqlx::query(&sql_text)
        .bind(&data.name)
        .bind(&data.model)
        .bind(&data.device_type)
        .bind(&data.power_type)
        .bind(&data.power_value)
        .bind(&data.price)
        .bind(&data.device_img)
        .bind(&data.remark)
        .bind(&data.id)
        .execute(pool)
        .await;

    match res {
        Ok(i) => println!("ok {:#?}", i),
        Err(e) => println!("err {:#?}", e),
    }
    Ok(())
}

// 删除
pub async fn delete_device(id: u64) -> Result<(), sqlx::Error> {
    let pool = get_pool().unwrap();
    let res = sqlx::query("DELETE FROM tb_device WHERE id=?")
        .bind(id)
        .execute(pool)
        .await;
    match res {
        Ok(i) => println!("{:#?}", i),
        Err(e) => println!("{:#?}", e),
    }
    Ok(())
}

// 详情
pub async fn device_detail(id: u64) -> Result<DeviceInfoRes, sqlx::Error> {
    let pool = get_pool().unwrap();
    let res = sqlx::query_as::<_, DeviceInfoRes>("SELECT * FROM tb_device WHERE id=?")
        .bind(id)
        .fetch_one(pool)
        .await;
    match res {
        Ok(i) => {
            println!("{:#?}", i);
            Ok(i)
        }
        Err(e) => {
            println!("{:#?}", e);
            Err(e)
        }
    }
}

/**
 * 查询 device 列表
 * 分页+模糊搜素+总条数
 */
pub async fn get_device_list_model(data: &DeviceSearch) -> Result<DeviceList, sqlx::Error> {
    let pool = get_pool().unwrap();
    let offset = (data.page - 1) * data.page_size;

    // 构建基本查询语句
    let mut query = "SELECT id, name, model, device_type, power_type, power_value, price, device_img, remark, created_at, updated_at FROM tb_device".to_string();

    // 构建搜索条件
    let mut conditions = Vec::new();

    if let Some(id) = &data.id {
        conditions.push(format!("id = '{}'", id));
    }

    if let Some(name) = &data.name {
        conditions.push(format!("name LIKE '%{}%'", name));
    }

    if let Some(model) = &data.model {
        conditions.push(format!("model LIKE '%{}%'", model));
    }

    if let Some(device_type) = &data.device_type {
        conditions.push(format!("device_type LIKE '%{}%'", device_type));
    }

    // 添加搜索条件到查询语句
    if !conditions.is_empty() {
        query.push_str(" WHERE ");
        query.push_str(&conditions.join(" AND "));
    }

    // 添加排序和分页限制
    query.push_str(" ORDER BY created_at DESC LIMIT ? OFFSET ?");

    println!("sql: {}", query);

    // 执行查询
    let device_list = sqlx::query_as::<_, DeviceInfoRes>(&query)
        .bind(data.page_size)
        .bind(offset)
        .fetch_all(pool)
        .await?;

    // 获取总条数
    let count_query = format!(
        "SELECT COUNT(*) as total FROM tb_device {}",
        if !conditions.is_empty() {
            format!("WHERE {}", conditions.join(" AND "))
        } else {
            "".to_string()
        }
    );
    let count_result: (Option<i64>,) = sqlx::query_as(count_query.as_str()).fetch_one(pool).await?;
    let count = count_result.0.unwrap_or(0); // 空结果处理

    let res = DeviceList {
        list: device_list,
        total: count,
    };
    Ok(res)
}
